﻿namespace etracks.Web.Controllers
{
    using etracks.EntityModels;
    using etracks.Helpers;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using System.Web.Mvc.Ajax;
    using System.Data.Linq;
    using System.Data.Common;

	[Authorize]
	public class ResourceController : BaseController
	{
		public ActionResult Index ()
		{
            ResourceIndexViewModel vm = new ResourceIndexViewModel();

            vm.Resources = ResourceController.GetResourceCount();

			return View (vm);
		}

        public ActionResult List(int? id)
        {
            ResourceListViewModel vm = new ResourceListViewModel();

            vm.ResourceList = ResourceController.GetResourceList(id);

            return this.List(vm);
        }

        public ActionResult View(int id)
        {
            ResourceReadOnlyViewModel vm = GetReadOnlyResource(id);

            return View(vm);
        }

        public ActionResult Add(int? id)
        {
            ResourceRegistrationViewModel vm = new ResourceRegistrationViewModel();

            if (id.HasValue)
            {
                //vm.ProjectId = id.Value;
                //vm.ProjectList = EnvironmentController.GetProjectList().Where(r => r.Id == id.Value).ToList();
                
                vm.ResourceTypeList = ResourceController.GetResourceTypeList();
            }
            else
            {
                vm.ResourceTypeList = ResourceController.GetResourceTypeList();
            }

            return View(vm);

        }

        [HttpPost]
        public ActionResult Add(ResourceRegistrationViewModel vm)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string, object>();
                parameters.Add("name", vm.Name);
                parameters.Add("alias", vm.Alias);
                parameters.Add("resource_type_id", vm.ResourceTypeId);

                PostgresHelper.Execute("INSERT INTO resource (name, alias, resource_type_id) VALUES (:name, :alias, :resource_type_id);", parameters);

                this.SuccessAlert("Resource added.");
            }
            catch (Exception)
            {
                throw;
            }

            vm.ResourceTypeList = ResourceController.GetResourceTypeList();

            return View(vm);
        }

        public ActionResult Edit(int id)
        {
            ResourceRegistrationViewModel vm = new ResourceRegistrationViewModel();

            Resource p = GetResource(id);

            if (p != null)
            {
                vm = new ResourceRegistrationViewModel
                {
                    Id= p.Id,
                    Name = p.Name, 
                    Alias = p.Alias,
                    ResourceTypeId = p.ResourceTypeId
                };
            }

            vm.ResourceTypeList = GetResourceTypeList();

            return View(vm);
        }

        [HttpPost]
        public ActionResult Edit(ResourceRegistrationViewModel vm)
        {
            try
            {
                Dictionary<string, object> parameters = new Dictionary<string,object>();
                parameters.Add("id", vm.Id);
                parameters.Add("name", vm.Name);
                parameters.Add("alias", vm.Alias);
                parameters.Add("resource_type_id", vm.ResourceTypeId);

                PostgresHelper.Execute("UPDATE Resource SET name = :name, alias = :alias, Resource_type_id = :resource_type_id WHERE id = :id;", parameters);

            }
            catch (Exception)
            {
                throw;
            }

            vm.ResourceTypeList = GetResourceTypeList();
            return View(vm);
        }

        private ActionResult List(ResourceListViewModel vm)
        {
            return View(vm);
        }

        public ActionResult Search()
        {
            return RedirectToAction("List");
        }

        [HttpPost]
        public ActionResult Search(string search_term)
        {
            ResourceSearchViewModel vm = new ResourceSearchViewModel();

            vm.Search_Term = search_term;

            vm.ResourceList = ResourceController.ResourceSearchResultsList(search_term);

            return View(vm);
        }

        public ActionResult Registration()
        {
            return View(new etracks.Web.ResourceRegistrationViewModel());
        }

        [HttpPost]
        public ActionResult Registration(ResourceRegistrationViewModel vm)
        {
            // Validate vm
            if (string.IsNullOrEmpty(vm.Name))
                ModelState.AddModelError("Name", "Cannot be empty.");

            if (ModelState.IsValid)
            {
                // Add student into list database records.
                string new_id = RegisterResource(vm.Name);
            }

            return View(vm);
        }

        ////////////////////////////////////////////////////////////////////////////////
        // Data retrieval hack functions
        private static int GetResourceCount()
        {
            int result = 0;
            object db_result = PostgresHelper.GetObject("SELECT COUNT(*) FROM resource;");

            if (db_result != null)
                int.TryParse(db_result.ToString(), out result);
            
            return result;
        }

        private static Resource GetResource(int id)
        {
            //List<Resource> result = new List<Resource>();
            Resource result = new Resource();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("id", id);

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM resource WHERE id = :id ORDER BY id LIMIT 1;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Resource
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias"),
                    ResourceTypeId = n.Field<int?>("resource_type_id")
                }).FirstOrDefault();
            }

            return result;
        }

        private static ResourceReadOnlyViewModel GetReadOnlyResource(int id)
        {
            ResourceReadOnlyViewModel result = new ResourceReadOnlyViewModel();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("id", id);

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT  p.*, pt.name AS ""resource_type_name"" 
FROM    resource p
LEFT OUTER JOIN 
        resource_type pt
        ON p.resource_type_id = pt.id
WHERE   p.id = :id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new ResourceReadOnlyViewModel
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias"),
                    ResourceTypeId = n.Field<int?>("resource_type_id"),
                    ResourceType = n.Field<string>("resource_type_name")
                }).FirstOrDefault();
            }

            return result;
        }

        private static List<ResourceReadOnlyViewModel> GetResourceList(int? id)
        {
            List<ResourceReadOnlyViewModel> result = new List<ResourceReadOnlyViewModel>();

            string whereClause = string.Empty;

            Dictionary<string, object> parameters = new Dictionary<string, object>();

            if (id.HasValue)
            {
                whereClause = string.Format("WHERE   er.environment_id = :environment_id");
                parameters.Add("environment_id", id.Value);
            }

            DataTable dt = PostgresHelper.GetDataTable(string.Format(@"SELECT  r.*, rt.name as ""resource_type_name""
FROM    environment_resource er
LEFT OUTER JOIN
        resource r
        ON er.resource_id = r.id
LEFT OUTER JOIN
        resource_type rt
        ON r.resource_type_id = rt.id
{0};", whereClause), parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new ResourceReadOnlyViewModel
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias"),
                    ResourceType = n.Field<string>("resource_type_name")
                }).ToList();
            }

            return result;
        }

        private static List<Resource> ResourceSearchResultsList(string search_term)
        {
            List<Resource> result = new List<Resource>();

            Dictionary<string, object> parameters = new Dictionary<string, object>();
            
            parameters.Add("search_term", string.Format("%{0}%", search_term));

            DataTable dt = PostgresHelper.GetDataTable(@"SELECT * FROM resource 
WHERE 
lower(id) LIKE lower(:search_term)
OR lower(name) LIKE lower(:search_term)
ORDER BY id;", parameters);

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new Resource
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name"),
                    Alias = n.Field<string>("alias")
                }).ToList();
            }

            return result;
        }

        private static string RegisterResource(string name)
        {
            Dictionary<string, object> parameters = new Dictionary<string, object>();
            parameters.Add("name", name);
            
            string sql = "INSERT INTO resource (name) VALUES (:name) RETURNING id;";

            object db_result = PostgresHelper.GetObject(sql, parameters);

            if (db_result != null)
                return db_result.ToString();
            else
                return null;

        }

        private static List<ResourceType> GetResourceTypeList()
        {
            List<ResourceType> result = new List<ResourceType>();

            DataTable dt = PostgresHelper.GetDataTable("SELECT * FROM resource_type ORDER BY id;");

            if (dt != null)
            {
                result = dt.AsEnumerable().Select(n => new ResourceType
                {
                    Id = n.Field<int>("id"),
                    Name = n.Field<string>("name")
                }).ToList();
            }

            return result;
        }
	}
}